import sqlite3, datetime, time
from django.shortcuts import render
from django.http import HttpResponse
from shared_resources import *
from collections import OrderedDict

def lesson_overview(request, staff_id, month):
	initialize(request)
	output = ""
	
	sql_connection = sqlite3.connect(global_vars["database"])
	sql_cursor = sql_connection.cursor()
	name = sql_cursor.execute("""select first_name, middle_name, last_name from staff where id = ?""", (staff_id, )).fetchone()
	
	if not request.session["rights"]["view_lesson_overview"]:
		output += '<p class="error_message">'+t('You do not have the rights to see the the lesson overview.')+'</p>'
	elif name == None:
		output += '<p class="error_message">'+t('Teacher doesn\'t exist.')+'</p>'
	else:

		request.session["overview_month"] = int(request.session.get('overview_month', (datetime.date.today().year-1970) * 12 + datetime.date.today().month - 1))
		
		if month != None:
			request.session['overview_month'] = int(month)

		output += '<br><input type="button" class="btn btn-primary btn-center" onclick="print_div(\'print_area\')" value="'+t('Print table')+'" /><br><br>'
        output += (
		'<div class="row">'
		'<div class="col-xs-4"><a class="btn btn-default center-block" role="button" href="/office/lesson-overview/'+str(staff_id)+'/'+str(request.session['overview_month']-1)+'/">'+t('Previous month')+'</a></div>'
		'<div class="col-xs-4"><h4 style="text-align: center;">'+stamp_to_display(month_to_stamp(request.session['overview_month']), "month_time_format")+'</h4></div> '
		'<div class="col-xs-4"><a class="btn btn-default center-block" role="button" href="/office/lesson-overview/'+str(staff_id)+'/'+str(request.session['overview_month']+1)+'/">'+t('Next month')+'</a></div>'
		'</div>'
		)
	
	classes = {}
	db_rows = sql_cursor.execute("""select id, subject, description from classes where teacher_id = ?""", (int(staff_id), ))
	for class_id, subject, description in db_rows:
		classes[class_id] = {"subject": subject, "description": description, "students": [], "lessons": []}
		
	for class_id, class_values in classes.iteritems():
		db_rows = sql_cursor.execute("""select student_id from student_classes where class_id = ?""", (class_id, ))
		for row in db_rows:
			classes[class_id]["students"].append(row[0])

	for class_id, class_values in classes.iteritems():
		db_rows = sql_cursor.execute("""select id, start_time, end_time from lessons where class_id = ? and start_time > ? and end_time < ?""", (class_id, month_to_stamp(request.session['overview_month']), month_to_stamp(request.session['overview_month']+1)))
		for id, start_time, end_time in db_rows:
			classes[class_id]["lessons"].append({"id": id, "start_time": start_time, "end_time": end_time, "feedback_given": True})

	for class_id, class_values in classes.iteritems():
		for lesson_key, lesson_values in enumerate(classes[class_id]["lessons"]):
			for student_id in class_values["students"]:
				result = sql_cursor.execute("""select id from student_feedback where student_id = ? and lesson_id = ?""", (student_id, lesson_values["id"])).fetchone()
				if result == None:
					classes[class_id]["lessons"][lesson_key]["feedback_given"] = False
					break
					
	
	output += '<div id="print_area">'
	output += '<h2>'+display_name(name[0], name[1], name[2])+"</h2><br /><br />"
	output += '<table class="text_table"><tr><td style="width: 150px;"><b>'+t('Date')+'</b></td><td style="width: 150px;"><b>'+t('Time')+'</b></td><td style="width: 150px;"><b>'+t('Lesson Details')+'</b></td><td><b>'+t('Feedback given')+'</b></td></tr>'

	sum = 0
	for class_id, class_values in classes.iteritems():
		for lesson_key, lesson_values in enumerate(classes[class_id]["lessons"]):
			output += "<tr><td>"+stamp_to_display(lesson_values["start_time"], "date_time_format")+"</td><td>"+stamp_to_time_amount(lesson_values["end_time"] - lesson_values["start_time"])+"</td><td>"+class_subjects[class_values["subject"]]+" - "+class_values["description"]+'</td><td>'+(t("Yes", (), True) if lesson_values["feedback_given"] else t("No", (), True))+"</td></tr>"
			sum += lesson_values["end_time"] - lesson_values["start_time"]

	output += (
	'<tr><td></br></td><td></td><td></td><td></td></tr>'
	'<tr><td><b>'+t('TOTAL TIME: ')+'</td><td></td><td></td><td><b>'+stamp_to_time_amount(sum)+'</b></td></tr>'
	'<tr><td></br></td><td></td><td></td><td></td></tr>'
	'<tr class="printOnly"><td><b>'+t('Signature: ')+'</td><td></td><td></td><td><b>_________________</b></td></tr>'
	'</table></div><br><br><input type="button" class="btn btn-primary btn-center" onclick="print_div(\'print_area\')" value="'+t('Print table')+'" />')

	return format_response(version["icons"]["sign"]+t("Lesson Overview"), output)

def log_overview(request, page):
	initialize(request)
	output = ""

	page = int(page) if page != None else 0
	logs_per_page = 25
	
	if not request.session["rights"]["view_log"]:
		output += '<p class="error_message">'+t('You do not have the rights to view the log.')+'</p>'
	else:

		sql_connection = sqlite3.connect(global_vars["database"])
		sql_cursor = sql_connection.cursor()    
		

		result = sql_cursor.execute("""SELECT count(staff_id) from log""").fetchone()
		
		pages = int((result[0]-1)/logs_per_page)
		
		output += '<table class="table viewuser table-striped table-hover"><tr><td><b>'+t('Staff')+'</b></td><td><b>'+t('Time')+'</b></td><td><b>'+t('Event')+'</b></td></tr>'
		
		db_rows = sql_cursor.execute("""SELECT staff_id, (select first_name from staff where id = staff_id), (select last_name from staff where id = staff_id), time, message FROM log order by time desc limit ?,?""", (logs_per_page*page, logs_per_page))
		for row in db_rows:
			output += ('<tr><td>'+display_name(row[1],"",row[2])+'</td><td>'+stamp_to_display(row[3] - global_vars["timezone_offset"], "date_time_format")+'</td><td>'+to_str(row[4])+'</td></tr>')

		output += '</table><br /><br />'
		
		if page > 0:
			output += '<a class="btn btn-primary" href="/office/log/'+str(page-1)+'/">'+t('Previous page')+'</a> '
			
		if page < pages:
			output += '<a class="btn btn-primary" style="float: right;" href="/office/log/'+str(page+1)+'/">'+t('Next page')+'</a> '
			
	return format_response(version["icons"]["log"]+t('Log page %s of %s', (str(page+1), str(pages+1))), output)	
	
def day_overview(request, view_day):
	initialize(request)
	output = ""

	if not request.session["rights"]["view_day_overview"]:
		output += '<p class="error_message">'+t('You do not have the rights to see the day overview.')+'</p>'
	else:
		sql_connection = sqlite3.connect(global_vars["database"])
		sql_cursor = sql_connection.cursor()

		request.session["day_overview_time"] = int(request.POST.get('day_overview_time', request.session.get('day_overview_time', 0)))
		
		if view_day != None:
			request.session['schedule_day'] = int(view_day)
		
		output += (
		'<div class="row">'
		'<div class="col-xs-4"><a class="btn btn-default center-block" role="button" href="/office/day-overview/'+str(request.session['schedule_day']-1)+'/">'+t('Previous day')+'</a></div>'
		'<div class="col-xs-4"><h4 style="text-align: center;">'+stamp_to_display(day_to_stamp(request.session['schedule_day']), "day_time_format")+'</h4></div> '
		'<div class="col-xs-4"><a class="btn btn-default center-block" role="button" href="/office/day-overview/'+str(request.session['schedule_day']+1)+'/">'+t('Next day')+'</a></div>'
		'</div>'
		)
		
		if request.session["day_overview_time"] == 0:
			granularity = 60*60
			nudge = 30*60
		else:
			granularity = 30*60
			nudge = 0
			
		lessons=OrderedDict()

		db_rows = sql_cursor.execute("""select name from classrooms order by name""")
		for row in db_rows:
			lessons[row[0]] = {}

		db_rows = sql_cursor.execute("""select start_time, end_time, id, (select name from classrooms where id = lessons.room_id), (select first_name from staff where id = (select teacher_id from classes where id = lessons.class_id)), room_id from lessons where room_id != -1 and start_time >= ? and end_time <= ? order by room_id""", (day_to_stamp(request.session['schedule_day']), day_to_stamp(request.session['schedule_day']+1)))
		for row in db_rows:
			time = nudge + int((-nudge + row[0] - day_to_stamp(request.session['schedule_day'])) / granularity) * granularity
			end_time = row[1] - day_to_stamp(request.session['schedule_day'])
			while time < end_time:
				lessons[row[3]][time] = {"id":row[2], "teacher_name":row[4], "room_id":row[5]}
				time += granularity

		output += '<div class="row" style="margin-top: 20px;"><div class="col-xs-12"><table class="timetable"><tr><td></td>'
		for room, lessons_info in lessons.iteritems():
			output += '<td class="schedule_headers"><b>'+str(room)+'</b></td>'
		output += '</tr>'
		
		time = int(9.5 * hour)
		time_end = int(20.5 * hour)
		even = True

		while(time <= time_end):
			output += '<tr><td>'+stamp_to_time(time)+'-'+stamp_to_time(time+granularity)+'</td>'
			for room, lessons_info in lessons.iteritems():
				key = time
				try:
					lessons_info[key]
					lesson_str = (
					'<a href="/office/classroom-schedule/'+str(lessons_info[key]["room_id"])+'/">'
					+t('Teacher: ')+'<br />'+to_str(lessons_info[key]["teacher_name"]) + '<br />'
					'</a>'
					)
					state = len(global_vars["schedule_states"])
				except:
					state = 0
				if state == 0:
					text = ""
				elif state >= len(global_vars["schedule_states"]):
					text = lesson_str
				else:
					text = global_vars["schedule_states"][state]
				output += '<td class="day_'+('schedule_lesson' if state != 0 else 'schedule_'+("even" if even else "odd")+"_row")+'">'+text+'</td>'
			
			output += '</tr>\n'
			time += granularity
			even = not even
		output += '</table>'
		
		if request.session["day_overview_time"] == 0:
			output += '<a style="float: right; margin-top: 20px;" class="btn btn-primary" role="button" href="#" onclick="post_request({\'day_overview_time\': 1}, \'/office/day-overview/\')">'+t('Switch to half hour view')+'</a>'
		
		if request.session["day_overview_time"] == 1:
			output += '<a style="float: right; margin-top: 20px;" class="btn btn-primary" role="button" href="#" onclick="post_request({\'day_overview_time\': 0}, \'/office/day-overview/\')">'+t('Switch to hour view')+'</a>'
		
		output += '</div></div>'
	
	return format_response(version["icons"]["do"]+t("Daily classroom overview"), output)
	
def teaching_hours_overview(request, month):
	initialize(request)
	output = ""

	if not request.session["rights"]["view_teaching_hours_overview"]:
		output += '<p class="error_message">'+t('You do not have the rights to see the the teaching hours overview.')+'</p>'
	else:
		sql_connection = sqlite3.connect(global_vars["database"])
		sql_cursor = sql_connection.cursor()

		request.session["overview_month"] = int(request.session.get('overview_month', (datetime.date.today().year-1970) * 12 + datetime.date.today().month - 1))
		
		if month != None:
			request.session['overview_month'] = int(month)

		output += (
		'<div class="row">'
		'<div class="col-xs-4"><a class="btn btn-default center-block" role="button" href="/office/teaching-hours-overview/'+str(request.session['overview_month']-1)+'/">'+t('Previous month')+'</a></div>'
		'<div class="col-xs-4"><h4 style="text-align: center;">'+stamp_to_display(month_to_stamp(request.session['overview_month']), "month_time_format")+'</h4></div> '
		'<div class="col-xs-4"><a class="btn btn-default center-block" role="button" href="/office/teaching-hours-overview/'+str(request.session['overview_month']+1)+'/">'+t('Next month')+'</a></div>'
		'</div>'
		)
	
	teachers = {}
	db_rows = sql_cursor.execute("""select id, first_name, middle_name, last_name from staff where teacher = 1""")
	for id, first_name, middle_name, last_name in db_rows:
		teachers[id] = {"name": display_name(first_name, middle_name, last_name), "teaching_time": 0}

	classes = {}
	db_rows = sql_cursor.execute("""select id, teacher_id from classes""")
	for id, teacher_id in db_rows:
		classes[id] = teacher_id

	db_rows = sql_cursor.execute("""select class_id, start_time, end_time from lessons where start_time > ? and end_time < ?""", (month_to_stamp(request.session['overview_month']), month_to_stamp(request.session['overview_month']+1)))
	for class_id, start_time, end_time in db_rows:
		teachers[classes[class_id]]["teaching_time"] += end_time - start_time
	
	output += '<br /><table class="newuser table"><tr><td><b>'+t('Teacher')+'</b></td><td><b>'+t('Hours Teaching')+'</b></td></tr>'
	sum = 0
	for id in teachers:
		output += '<tr><td><a href="/office/staff-schedule/'+str(id)+'/">'+teachers[id]["name"]+"</a></td><td>"+stamp_to_time_amount(teachers[id]["teaching_time"])+"</td></tr>"
		sum += teachers[id]["teaching_time"]
	output += """
	<tr><td></td><td></td></tr>
	<tr><td><b>"""+t("Sum")+"""</b></td><td>"""+stamp_to_time_amount(sum)+"""</td></tr>
	</table><br />"""
	
	return format_response(version["icons"]["tho"]+t("Monthly Teaching Overview"), output)
